package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.TemplateDao;
import com.mp.approvalreq.entity.*;
import com.mp.approvalreq.entity.GeneralRequest.GeneralRequest;
import com.mp.approvalreq.util.*;
import com.mp.approvalreq.util.Formatter;
import com.mp.comment.entity.Comment;
import com.mp.user.entity.User;
import com.plusmoney.exception.RpcServerLogicalException;
import com.plusmoney.exception.enums.ApprovalRespCodeDesc;
import com.plusmoney.jdbc.util.DBUtils;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.RoundingMode;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class TemplateDaoImpl extends AbstractDao implements TemplateDao {

    private Logger logger = LoggerFactory.getLogger(TemplateDaoImpl.class);

    private Connection conn;

    public TemplateDaoImpl(Connection connection) {
        this.conn = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select Ftemplate_name, Ftemplate_meta_name, Ftemplate_tips, Ftemplate_component, Fcompany_id,"
                + " Fdepartment_id, Fmulti_chosen, Ftemplate_color, Fversion, Ftemplate_default_type,"
                + " Famount_top, Ftemplate_id, Fis_related_to_cost_analysis, Fpriority_show, Fis_allowed_to_edit_judger,"
                + " Frequest_flow, Fcc, Fancestor_id"
                + " from request_template";
        return String.format("%s %s", query, condition);
    }

    @Override
    Template getObject(ResultSet rs) throws SQLException {
        Template template = new Template();
        template.setTemplateName(rs.getString(1));
        template.setTemplateMetaName(rs.getString(2));
        template.setTemplateTips(rs.getString(3));
        template.setTemplateComponent(rs.getString(4));
        template.setCompanyId(rs.getInt(5));

        template.setDepartmentId(rs.getString(6));
        template.setMultiChosen(rs.getString(7));
        template.setTemplateColor(rs.getString(8));
        template.setVersion(rs.getInt(9));
        template.setDefaultType(rs.getInt(10));

        template.setAmountTop(rs.getBigDecimal(11));
        template.setTemplateId(rs.getInt(12));
        template.setIsRelatedToCostAnalysis(rs.getInt(13));
        template.setPriorityShow(rs.getString(14));
        template.setIsAllowedToEditJudger(rs.getInt(15));

        template.setRequestFlow(rs.getString(16));
        template.setCc(rs.getString(17));
        template.setAncestorId(rs.getInt(18));
        return template;
    }

    private String getQueryDetailSql(String condition) {
        final String query = "select Ftemplate_id, Ftemplate_name, Ftemplate_meta_name, Ftemplate_default_type, Ftemplate_tips,"
                + " Ftemplate_component, Fcompany_id, Fdepartment_id, Fstatus, Frequest_flow,"
                + " Fcc, Fmulti_chosen, Ftemplate_color, Fversion, Famount_top,"
                + " Fcreatetime, Fdisabletime, Fancestor_id, Fis_max_time, Fmax_time_permonth,"
                + " Fcontent_time_limit, Frange_specific, Fcompany_info_id_range, Fis_allow_add_temp_cc, Fis_related_to_cost_analysis,"
                + " Fpriority_show, Fas_general_template, Fmust_relate, Frelate_analysis_num, Fis_related_to_worktime,"
                + " Fgeneration, Fcomponent_group_json, Fmoney_amount_nums, Fout_sign_offset, Fparticipant_required,"
                + " Fproxy_contactIds, Frela_people_num, Fis_allowed_to_edit_judger, Fis_financial, Ffinancial_flow"
                + " from request_template";
        return String.format("%s %s", query, condition);
    }

    private Template getDetailObject(ResultSet rs) throws SQLException {
        Template template = new Template();
        template.setTemplateId(rs.getInt(1));
        template.setTemplateName(rs.getString(2));
        template.setTemplateMetaName(rs.getString(3));
        template.setDefaultType(rs.getInt(4));
        template.setTemplateTips(rs.getString(5));

        template.setTemplateComponent(rs.getString(6));
        template.setCompanyId(rs.getInt(7));
        template.setDepartmentId(rs.getString(8));
        template.setStatus(rs.getInt(9));
        template.setRequestFlow(rs.getString(10));

        template.setCc(rs.getString(11));
        template.setMultiChosen(rs.getString(12));
        template.setTemplateColor(rs.getString(13));
        template.setVersion(rs.getInt(14));
        template.setAmountTop(rs.getBigDecimal(15));

        template.setCreateTime(getTimeStr(rs.getTimestamp(16)));
        template.setDisabletime(getTimeStr(rs.getTimestamp(17)));
        template.setAncestorId(rs.getInt(18));
        template.setIsMaxTime(rs.getInt(19));
        template.setMaxTimePermonth(rs.getInt(20));

        template.setContentTimeLimit(rs.getInt(21));
        template.setRangeSpecific(rs.getString(22));
        template.setCompanyInfoIdRange(rs.getString(23));
        template.setIsAllowAddTempCc(rs.getInt(24));
        template.setIsRelatedToCostAnalysis(rs.getInt(25));

        template.setPriorityShow(rs.getString(26));
        template.setAsGeneralTemplate(rs.getInt(27));
        template.setMustRelate(rs.getInt(28));
        template.setRelateAnalysisNum(rs.getString(29));
        template.setIsRelateWorkTime(rs.getInt(30));

        template.setTemplateGeneration(rs.getInt(31));
        template.setComponentGroupJson(rs.getString(32));
        template.setMoneyAmountNums(rs.getString(33));
        template.setOutSignOffset(rs.getString(34));
        template.setParticipantRequired(rs.getInt(35));

        template.setProxyContactIds(rs.getString(36));
        template.setRelaPeopleNum(rs.getInt(37));
        template.setIsAllowedToEditJudger(rs.getInt(38));
        template.setIsFinancial(rs.getInt(39));
        template.setFinancialFlow(rs.getString(40));
        return template;
    }

    @Override
    public Template selectTemplateDetailById(Integer templateId) throws Throwable {
        String sql = getQueryDetailSql("where Ftemplate_id = ?");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, templateId);
            logger.info(SQLLogger.logSQL(sql, templateId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return getDetailObject(rs);
                }
            }
        }
        return null;
    }

    @Override
    public List<Template> selectAllEffectiveTemplate(Integer companyId) throws Throwable {
        List<Template> list = new ArrayList<>();
        String sql = getQuerySql("where Fcompany_id = ? and Fstatus = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public void updateTemplateFlowAndCc(List<Template> templates) throws Throwable {
        String sql = "update request_template set Frequest_flow = ?, Fcc = ? where Ftemplate_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Template template : templates) {
                ps.setString(1, template.getRequestFlow());
                ps.setString(2, template.getCc());
                ps.setInt(3, template.getTemplateId());
                logger.info(SQLLogger.logSQL(sql, template.getRequestFlow(), template.getCc(), template.getTemplateId()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public Map<Integer, Template> selectTemplateByIds(String templateIds) throws Throwable {
        Map<Integer, Template> map = new HashMap<>();
        if (StringUtils.isEmpty(templateIds)) {
            return map;
        }
        String sql = getQuerySql("where Ftemplate_id in(" + templateIds + ")");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = getObject(rs);
                    map.put(template.getTemplateId(), template);
                }
            }
        }
        return map;
    }


    @Override
    public List<Request> getAllPeopleOfApproval(List<Integer> approvalIdList) throws Throwable {
        List<Request> result = new LinkedList<>();
        StringBuilder sql = new StringBuilder("select Frequest_id, Fapplicant, Fapplicant_ciid, Fall_judger, Fall_judger_ciid,"
                + " Fcc, Fcc_ciid, Frela_people, Frela_people_ciid, Ffinished"
                + " from request_flow where Frequest_id in (");
        for (int i = 0; i < approvalIdList.size(); i++) {
            sql.append(approvalIdList.get(i));
            if (approvalIdList.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request temp = new Request();
                    temp.setRequestDkey(rs.getInt(1));
                    temp.setApplicant(rs.getInt(2));
                    temp.setApplicantCiid(rs.getInt(3));
                    temp.setAllJudger(rs.getString(4));
                    temp.setAllJudgerCiid(rs.getString(5));

                    temp.setCc(rs.getString(6));
                    temp.setCcCiid(rs.getString(7));
                    temp.setRelaPeople(rs.getString(8));
                    temp.setRelaPeopleCiid(rs.getString(9));
                    temp.setFinished(rs.getInt(10));

                    result.add(temp);
                }
            }
        }
        return result;
    }

    @Override
    public List<Template> getAllOfMyCompanyTemplate(User user) throws Throwable {
        List<Template> list = new ArrayList<>();
        String sql = "select Ftemplate_id, Ftemplate_name, Ftemplate_color, Fversion, Ftemplate_default_type,"
                + " Famount_top, Fdepartment_id, Fancestor_id, Fmax_time_permonth, Fcontent_time_limit,"
                + " Fis_max_time, Fcc "
                + " from approval.request_template where Fcompany_id = ? and Fstatus = 1";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, user.getCompanyId());
            logger.info(Formatter.aformat(sql, user.getCompanyId()));
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setTemplateName(rs.getString(2));
                    template.setTemplateColor(rs.getString(3));
                    template.setVersion(rs.getInt(4));
                    template.setDefaultType(rs.getInt(5));
                    template.setAmountTop(rs.getBigDecimal(6));
                    template.setDepartmentId(rs.getString(7));
                    template.setAncestorId(rs.getInt(8));
                    template.setMaxTimePermonth(rs.getInt(9));
                    template.setContentTimeLimit(10);
                    template.setIsMaxTime(rs.getInt(11));
                    template.setCc(rs.getString(12));

                    template.setCompanyId(user.getCompanyId());
                    list.add(template);
                }
            }
        }
        return list;
    }

    @Override
    public List<Template> getTemplateFitToWholeCompanyRangeList(User user) throws Throwable {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        PreparedStatement pu = null;
        ResultSet ru = null;
        try {
            String sql = "select Ftemplate_id,Ftemplate_name,Ftemplate_color,Fversion,Ftemplate_default_type,"
                    + " Famount_top,Fdepartment_id,Fancestor_id,Fmax_time_permonth,Fcontent_time_limit,Fis_max_time "
                    + " from approval.request_template where Fcompany_id =? and Fstatus = 1";
            List<Template> list = new ArrayList<>();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, user.getCompanyId());
            logger.info(Formatter.aformat(sql, user.getCompanyId()));
            rs = pstm.executeQuery();
            while (rs.next()) {
                Template t = new Template();
                t.setTemplateId(rs.getInt(1));
                t.setTemplateName(rs.getString(2));
                t.setTemplateColor(rs.getString(3));
                t.setVersion(rs.getInt(4));
                t.setDefaultType(rs.getInt(5));
                t.setAmountTop(rs.getBigDecimal(6));
                t.setDepartmentId(rs.getString(7));
                t.setAncestorId(rs.getInt(8));
                t.setMaxTimePermonth(rs.getInt(9));
                t.setContentTimeLimit(10);
                t.setIsMaxTime(rs.getInt(11));
                list.add(t);
            }
            //没有模板，添加默认模板
            if (list.size() == 0) {
                String everUse = "select count(*) from approval.request_template where Fcompany_id = ?";
                pu = conn.prepareStatement(everUse);
                pu.setInt(1, user.getCompanyId());
                logger.info(Formatter.aformat(everUse, user.getCompanyId()));
                ru = pu.executeQuery();
                if (ru.next()) {
                    if (ru.getInt(1) == 0) {
                        return null;
                    }
                }
            }
            return list;
        } finally {
            DBUtils.releaseResultSet(rs, ru);
            DBUtils.releasePreparedStatement(pstm, pu);
        }
    }

    @Override
    public Template getTemplate(Integer templateId, Integer companyId) throws SQLException {
        Template template = new Template();

        String sql = "select Frequest_flow, Fmulti_chosen, Fstatus, Ftemplate_default_type, Famount_top"
                + " from request_template where Ftemplate_id = ? and Fcompany_id = ? ";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, templateId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, templateId, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    template.setRequestFlow(rs.getString(1));
                    template.setMultiChosen(rs.getString(2));
                    template.setStatus(rs.getInt(3));
                    template.setDefaultType(rs.getInt(4));
                    template.setAmountTop(rs.getBigDecimal(5));

                    template.setCompanyId(companyId);
                    template.setTemplateId(templateId);
                }
            }
        }

        return template;
    }

    @Override
    public Template getDefaultTemplateDetail(Integer templateId) throws SQLException {
        Template template = new Template();

        String sql = "select Ftemplate_name, Ftemplate_meta_name, Ftemplate_tips, Ftemplate_component, "
                + " Fcompany_id, Fdepartment_id, Fmulti_chosen, Ftemplate_color, Ftemplate_default_type, "
                + " Famount_top, Frequest_flow, Fcc, Fancestor_id, Fmax_time_permonth, Fcontent_time_limit, "
                + " Frela_request_num, Frange_specific, Fis_allow_add_temp_cc, Fis_related_to_cost_analysis, "
                + " Fis_allowed_to_edit_judger, Frela_people_num, Fpriority_show, Fout_sign_offset, Fparticipant_required "
                + " from approval.request_template where Ftemplate_id = ? and Fstatus = 1 and Fversion = 0 ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, templateId);
            logger.info(SQLLogger.logSQL(sql, templateId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    template.setTemplateName(rs.getString(1));
                    template.setTemplateMetaName(rs.getString(2));
                    template.setTemplateTips(rs.getString(3));
                    template.setTemplateComponent(rs.getString(4));
                    template.setCompanyId(rs.getInt(5));
                    template.setDepartmentId(rs.getString(6));
                    template.setMultiChosen(rs.getString(7));
                    template.setTemplateColor(rs.getString(8));
                    template.setDefaultType(rs.getInt(9));
                    template.setAmountTop(rs.getBigDecimal(10));
                    template.setRequestFlow(rs.getString(11));
                    template.setCc(rs.getString(12));
                    template.setAncestorId(rs.getInt(13));
                    template.setMaxTimePermonth(rs.getInt(14));
                    template.setContentTimeLimit(rs.getInt(15));
                    template.setRelaRequestNum(rs.getInt(16));
                    template.setRangeSpecific(rs.getString(17));
                    template.setIsAllowAddTempCc(rs.getInt(18));
                    template.setIsRelatedToCostAnalysis(rs.getInt(19));
                    template.setIsAllowedToEditJudger(rs.getInt(20));
                    template.setRelaPeopleNum(rs.getInt(21));
                    template.setPriorityShow(rs.getString(22));
                    template.setOutSignOffset(rs.getString(23));
                    template.setParticipantRequired(rs.getInt(24));
                }
            }
        }

        return template;
    }

    @Override
    public Request getTemplateDetailByRequests(Request request, User user) throws Throwable {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            String sql = "select "
                    + " Ftemplate_name, Ftemplate_meta_name, Ftemplate_tips, Ftemplate_component, Fcompany_id,"
                    + " Fdepartment_id, Fmulti_chosen, Ftemplate_color, Fversion, Ftemplate_default_type,"
                    + " Famount_top, Fancestor_id, Frela_request_num, Fis_allow_add_temp_cc, Fis_allowed_to_edit_judger,"
                    + " Fgeneration, Fmoney_amount_nums, Fis_financial"
                    + " from approval.request_template"
                    + " where Ftemplate_id = ? and Fcompany_id=?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, request.getTemplateId());
            pstm.setInt(2, user.getCompanyId());

            logger.info(Formatter.aformat(sql, request.getTemplateId(), user.getCompanyId()));
            rs = pstm.executeQuery();

            if (rs.next()) {
                request.setTemplateName(rs.getString(1));
                request.setTemplateMetaName(rs.getString(2));
                request.setTemplateTips(rs.getString(3));
                request.setTemplateComponent(rs.getString(4));
                request.setCompanyId(rs.getInt(5));

                request.setDepartmentId(rs.getString(6));
                request.setMultiChosen(rs.getString(7));
                request.setTemplateColor(rs.getString(8));
                request.setVersion(rs.getInt(9));
                request.setDefaultType(rs.getInt(10));

                request.setAmountTop(rs.getBigDecimal(11));
                request.setAncestorId(rs.getInt(12));
                request.setRelaRequestNum(rs.getInt(13));
                request.setIsAllowAddTempCc(rs.getInt(14));
                request.setIsAllowedToEditJudger(rs.getInt(15));

                request.setTemplateGeneration(rs.getInt(16));
                request.setMoneyAmountNums(rs.getString(17));
                request.setIsFinancial(rs.getInt(18));
                return request;
            } else {
                throw new RpcServerLogicalException(ApprovalRespCodeDesc.MISS_TEMPLATE);
            }
        } finally {
            DBUtils.releaseResultSet(rs);
            DBUtils.releasePreparedStatement(pstm);
        }
    }


    @Override
    public Template getAncestorTemplateSimpleInfo(Integer templateId, Integer companyId) throws SQLException {
        Template template = new Template();

        String sql = " select Fancestor_id, Frequest_flow, Fversion, Fstatus from approval.request_template where Ftemplate_id = ? and Fcompany_id = ? ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, templateId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, templateId, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    template.setAncestorId(rs.getInt(1));
                    template.setRequestFlow(rs.getString(2));
                    template.setVersion(rs.getInt(3));
                    template.setStatus(rs.getInt(4));
                }
            }
        }

        return template;
    }

    @Override
    public void disableTemplate(Integer templateId, Integer companyId) throws SQLException {

        String sql = " update approval.request_template set Fstatus = 0, Fdisabletime = NOW()"
                + " where Ftemplate_id = ? and Fcompany_id = ? limit 1";

        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, templateId);
            pstm.setInt(2, companyId);

            SQLLogger.logSQL(sql, templateId, companyId);

            pstm.executeUpdate();
        }
    }

    @Override
    public void insertOriginTemplate(Template template, User user, int generation) throws Throwable {
        PreparedStatement ps = null;
        // 初始化数值为空的Integer
        ObjectUtils.initInteger(template);

        try {
            String sql = " insert into approval.request_template set"
                    + " Ftemplate_name = ?, Ftemplate_meta_name = ?, Ftemplate_default_type = ?, Ftemplate_tips = ?, Ftemplate_component = ?,"
                    + " Fcompany_id = ?, Fdepartment_id = ?, Frequest_flow = ?,  Fcc = ?, Fmulti_chosen = ?,"
                    + " Ftemplate_color = ?, Fversion = ?, Famount_top = ?, Fancestor_id = ?,  Fmax_time_permonth = ?,"
                    + " Fcontent_time_limit = ?, Frela_request_num = ?, Frange_specific = ?,  Fis_max_time = ?, Fis_allow_add_temp_cc = ?,"
                    + " Fis_related_to_cost_analysis = ?, Fis_allowed_to_edit_judger = ?, Frela_people_num = ?, Ftemplate_id = ?, Fpriority_show = ?,"
                    + " Fas_general_template = ? ,Fmust_relate = ?, Frelate_analysis_num = ?, Fis_related_to_worktime = ?,  Fcompany_info_id_range = ?,"
                    + " Fgeneration = ?, Fcomponent_group_json = ?, Fmoney_amount_nums = ?,  Fout_sign_offset = ?, Fparticipant_required = ?,"
                    + " Fproxy_contactIds = ?, Fis_financial = ?, Ffinancial_flow = ?, Fstatus = 1, Fcreatetime = NOW()";

            ps = conn.prepareStatement(sql);
            ps.setString(1, template.getTemplateName());
            ps.setString(2, template.getTemplateMetaName());
            ps.setInt(3, template.getDefaultType());
            ps.setString(4, template.getTemplateTips());
            ps.setString(5, template.getTemplateComponent());

            ps.setInt(6, user.getCompanyId());
            ps.setString(7, template.getDepartmentId());
            ps.setString(8, template.getRequestFlow());
            ps.setString(9, template.getCc());
            ps.setString(10, template.getMultiChosen());

            ps.setString(11, template.getTemplateColor());
            ps.setInt(12, template.getVersion());
            ps.setBigDecimal(13, template.getAmountTop());
            ps.setInt(14, template.getAncestorId());
            ps.setInt(15, template.getMaxTimePermonth());

            ps.setInt(16, template.getContentTimeLimit());
            ps.setInt(17, template.getRelaRequestNum());
            ps.setString(18, template.getRangeSpecific());
            ps.setInt(19, template.getIsMaxTime());
            ps.setInt(20, template.getIsAllowAddTempCc());

            ps.setInt(21, template.getIsRelatedToCostAnalysis());
            ps.setInt(22, template.getIsAllowedToEditJudger());
            ps.setInt(23, template.getRelaPeopleNum());
            ps.setInt(24, template.getTemplateInsertId());
            ps.setString(25, template.getPriorityShow());

            ps.setInt(26, template.getAsGeneralTemplate());
            ps.setInt(27, template.getMustRelate());                 // 强制关联客户或任务
            ps.setString(28, template.getRelateAnalysisNum());       // 金额组件计入项
            ps.setInt(29, template.getIsRelateWorkTime());
            ps.setString(30, template.getCompanyInfoIdRange());

            ps.setInt(31, generation);
            ps.setString(32, template.getComponentGroupJson());
            ps.setString(33, template.getMoneyAmountNums());
            ps.setString(34, template.getOutSignOffset());
            ps.setInt(35, template.getParticipantRequired());

            ps.setString(36, template.getProxyContactIds());
            ps.setInt(37, template.getIsFinancial());
            ps.setString(38, template.getFinancialFlow());

            logger.info(Formatter.aformat(sql,
                    template.getTemplateName(), template.getTemplateMetaName(), template.getDefaultType(), template.getTemplateTips(), template.getTemplateComponent(),
                    user.getCompanyId(), template.getDepartmentId(), template.getRequestFlow(), template.getCc(), template.getMultiChosen(),
                    template.getTemplateColor(), template.getVersion(), template.getAmountTop(), template.getAncestorId(), template.getMaxTimePermonth(),
                    template.getContentTimeLimit(), template.getRelaRequestNum(), template.getRangeSpecific(), template.getIsMaxTime(), template.getIsAllowAddTempCc(),
                    template.getIsRelatedToCostAnalysis(), template.getIsAllowedToEditJudger(), template.getRelaPeopleNum(), template.getTemplateInsertId(), template.getPriorityShow(), template.getAsGeneralTemplate(),
                    template.getMustRelate(), template.getRelateAnalysisNum(), template.getIsRelateWorkTime(), template.getCompanyInfoIdRange(), generation,
                    template.getComponentGroupJson(), template.getMoneyAmountNums(), template.getOutSignOffset(), template.getParticipantRequired(),
                    template.getProxyContactIds(), template.getIsFinancial(), template.getFinancialFlow()));

            ps.executeUpdate();

        } finally {
            DBUtils.releasePreparedStatement(ps);
        }
    }

    @Override
    public void deleteTemplate(Template template, User user) throws Throwable {
        PreparedStatement pstm = null;
        try {
            String sql = "update approval.request_template set Fstatus = 0, Fdisabletime = now() where Ftemplate_id = ? and Fcompany_id = ? and Fstatus = 1";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, template.getTemplateId());
            pstm.setInt(2, user.getCompanyId());
            logger.info(Formatter.aformat(sql, template.getTemplateId(), user.getCompanyId()));
            pstm.executeUpdate();
        } finally {
            DBUtils.releasePreparedStatement(pstm);
        }
    }

    @Override
    public List<RefuseChain> getRefuseChainDetail(String refuseChainIds) throws SQLException {
        List<RefuseChain> refuseChains = new ArrayList<>();

        // order by Frequest_id 目的: 驳回请求的先后顺序
        String sql = " select Frequest_id, Frequest_templet, Fversion from approval.request_flow where Frequest_id in(" + refuseChainIds + ") order by Frequest_id ";
        SQLLogger.logSQL(sql);

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    RefuseChain refuseChain = new RefuseChain();
                    refuseChain.setRequestId(rs.getInt(1));
                    refuseChain.setTemplateId(rs.getInt(2));
                    refuseChain.setVersion(rs.getInt(3));
                    refuseChains.add(refuseChain);
                }
            }
        }

        return refuseChains;
    }

    @Override
    public Request getLatestTemplateByAncestorId(Request request) throws Throwable {
        String sql = "select Ftemplate_id,Fversion,Ftemplate_default_type from approval.request_template " +
                " where Fancestor_id = ? and Fcompany_id = ? and Fstatus = 1 order by Ftemplate_id desc limit 1";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, request.getAncestorId());
            ps.setInt(2, request.getCompanyId());
            logger.info(Formatter.aformat(sql, request.getAncestorId(), request.getCompanyId()));
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    request.setLatestTemplateId(rs.getInt(1));
                    request.setLatestTemplateVersion(rs.getInt(2));
                    request.setLatestDefaultType(rs.getInt(3));
                }
            }
            return request;
        }
    }

    @Override
    public List<Template> selectCostAnalysisTemplates(User user) throws Throwable {
        String sql = "select Ftemplate_id, Ftemplate_name, Ftemplate_color, Fversion, Ftemplate_default_type,"
                + " Famount_top, Fdepartment_id, Fancestor_id, Fmax_time_permonth, Fcontent_time_limit,"
                + " Fis_max_time"
                + " from approval.request_template "
                + " where Fcompany_id = ? and Fis_related_to_cost_analysis = 1 ";
        List<Template> list = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, user.getCompanyId());
            logger.info(Formatter.aformat(sql, user.getCompanyId(), user.getDeptId(), user.getContactId()));
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setTemplateName(rs.getString(2));
                    template.setTemplateColor(rs.getString(3));
                    template.setVersion(rs.getInt(4));
                    template.setDefaultType(rs.getInt(5));
                    template.setAmountTop(rs.getBigDecimal(6));
                    template.setDepartmentId(rs.getString(7));
                    template.setAncestorId(rs.getInt(8));
                    template.setMaxTimePermonth(rs.getInt(9));
                    template.setContentTimeLimit(10);
                    template.setIsMaxTime(rs.getInt(11));
                    list.add(template);
                }
            }
        }
        return list;
    }

    @Override
    public List<Template> selectCostAnalysisTemplates(Request request) throws Throwable {
        String sql = "select Ftemplate_id,Ftemplate_name,Ftemplate_color,Fversion,Ftemplate_default_type,Famount_top,Fdepartment_id,"
                + "Fancestor_id,Fmax_time_permonth,Fcontent_time_limit,Fis_max_time from approval.request_template "
                + "where Fcompany_id =? and Fis_related_to_cost_analysis = 1 ";
        List<Template> list = new ArrayList<>();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, request.getCompanyId());
            logger.info(Formatter.aformat(sql, request.getCompanyId()));
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Template t = new Template();
                    t.setTemplateId(rs.getInt(1));
                    t.setTemplateName(rs.getString(2));
                    t.setTemplateColor(rs.getString(3));
                    t.setVersion(rs.getInt(4));
                    t.setDefaultType(rs.getInt(5));
                    t.setAmountTop(rs.getBigDecimal(6));
                    t.setDepartmentId(rs.getString(7));
                    t.setAncestorId(rs.getInt(8));
                    t.setMaxTimePermonth(rs.getInt(9));
                    t.setContentTimeLimit(10);
                    t.setIsMaxTime(rs.getInt(11));
                    list.add(t);
                }
            }
        }
        return list;
    }

    @Override
    public List<Request> getRequestByTemplates(List<Template> templateIdList, Request request) throws Throwable {
        List<Request> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select Frequest_id,Frequest_templet,Frequest_template_type,Fversion "
                + " from approval.request_flow where Ffinished in(1,2) "
                + " and Frequest_templet in (");
        for (int i = 0; i < templateIdList.size(); i++) {
            sb.append(templateIdList.get(i).getTemplateId());
            if (templateIdList.size() - i > 1) {
                sb.append(",");
            }
        }
        sb.append(") and Fapplicant = ? and date_format(Ffinally_confirmed_time,'%Y-%m-%d') >= ? "
                + " and date_format(Ffinally_confirmed_time,'%Y-%m-%d') <= ?");
        String sql = sb.toString();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, request.getApplicant());
            pstm.setString(2, request.getSearchStartTime());
            pstm.setString(3, request.getSearchEndTime());
            logger.info(Formatter.aformat(sql, request.getApplicant(), request.getSearchStartTime(), request.getSearchEndTime()));
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Request re = new Request();
                    re.setRequestDkey(rs.getInt(1));
                    re.setTemplateId(rs.getInt(2));
                    re.setDefaultType(rs.getInt(3));
                    re.setVersion(rs.getInt(4));
                    list.add(re);
                }
            }
        }
        return list;
    }

    @Override
    public List<Request> getRequestByTemplatesWithSearchRangeAndCurrentMonths(List<Template> templateIdList, Request request) throws Throwable {
        StringBuilder sb = new StringBuilder("select Frequest_id,Frequest_templet,Frequest_template_type, "
                + " Fversion,Fapplicant,date_format(Ffinally_confirmed_time,'%Y-%m'),Ftotal_money "
                + " from approval.request_flow where Ffinished > 0 "
                + " and Ffinished in(1,2) "
                + " and Frequest_templet in (");
        List<Object> con = new ArrayList<>();
        for (int i = 0; i < templateIdList.size(); i++) {
            sb.append(templateIdList.get(i).getTemplateId());
            if (templateIdList.size() - i > 1) {
                sb.append(",");
            }
        }
        sb.append(") and Fapplicant in(");
        String srStr = request.getSearchRange();
        String[] srs = srStr.split(",");
        for (int i = 0; i < srs.length; i++) {
            sb.append(Integer.parseInt(srs[i]));
            if (srs.length - i > 1) {
                sb.append(",");
            }
        }
        sb.append(")");
        if (request.getCurrentMonths() != null) {
            if (Objects.equals(request.getConfirmTimeOrCreateTime(), 1)) {
                sb.append(" and date_format(Fcreate_time,'%Y-%m') in(");
            } else {
                sb.append(" and date_format(Ffinally_confirmed_time,'%Y-%m') in(");
            }
            String cMonths = request.getCurrentMonths();
            String[] months = cMonths.split(",");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
            for (int i = 0; i < months.length; i++) {
                sdf.parse(months[i]);
                sb.append(Formatter.aformat("'?'", months[i]));
                if (months.length - i > 1) {
                    sb.append(",");
                }
            }
            sb.append(") ");
        }
        if (request.getCurrentDays() != null) {
            if (Objects.equals(request.getConfirmTimeOrCreateTime(), 1)) {
                sb.append(" and date_format(Fcreate_time,'%Y-%m-%d') in(");
            } else {
                sb.append(" and date_format(Ffinally_confirmed_time,'%Y-%m-%d') in(");
            }
            String cDays = request.getCurrentDays();
            String[] days = cDays.split(",");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 0; i < days.length; i++) {
                sdf.parse(days[i]);
                sb.append(Formatter.aformat("'?'", days[i]));
                if (days.length - i > 1) {
                    sb.append(",");
                }
            }
            sb.append(") ");
        }
        if (request.getSearchStartTime() != null) {
            if (Objects.equals(request.getConfirmTimeOrCreateTime(), 1)) {
                sb.append(" and date_format(Fcreate_time,'%Y-%m-%d') >= ?");
            } else {
                sb.append(" and date_format(Ffinally_confirmed_time,'%Y-%m-%d') >= ?");
            }
            con.add(request.getSearchStartTime());
        }
        if (request.getSearchEndTime() != null) {
            if (Objects.equals(request.getConfirmTimeOrCreateTime(), 1)) {
                sb.append(" and date_format(Fcreate_time,'%Y-%m-%d') <= ?");
            } else {
                sb.append(" and date_format(Ffinally_confirmed_time,'%Y-%m-%d') <= ?");
            }
            con.add(request.getSearchEndTime());
        }
        Object[] conO = con.toArray();

        List<Request> list = new ArrayList<>();
        String sql = sb.toString();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            if (conO.length > 0) {
                PstmSetter.setCondition(sql, ps, conO);
            } else {
                logger.info(sql);
            }
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request re = new Request();
                    re.setRequestDkey(rs.getInt(1));
                    re.setTemplateId(rs.getInt(2));
                    re.setDefaultType(rs.getInt(3));
                    re.setVersion(rs.getInt(4));
                    re.setApplicant(rs.getInt(5));
                    re.setFinallyConfirmedTime(rs.getString(6));
                    re.setTotalMoney(rs.getString(7));
                    list.add(re);
                }
            }
        }
        return list;
    }

    @Override
    public Template getTemplateDetailWithoutVersion(Template template)
            throws Throwable {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            String sql = "select Ftemplate_name,Ftemplate_meta_name,Ftemplate_tips,Ftemplate_component,"
                    + "Fcompany_id,Fdepartment_id,Fmulti_chosen,Ftemplate_color,Ftemplate_default_type,Famount_top,"
                    + "Frequest_flow,Fcc,Fmax_time_permonth,Fcontent_time_limit,Frela_request_num,Frange_specific,"
                    + "Fis_allow_add_temp_cc,Fis_related_to_cost_analysis,Fis_allowed_to_edit_judger,Fversion,Fancestor_id "
                    + "from approval.request_template "
                    + "where Ftemplate_id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, template.getTemplateId());
            logger.info(Formatter.aformat(sql, template.getTemplateId()));
            rs = pstm.executeQuery();
            if (rs.next()) {
                template.setTemplateName(rs.getString(1));
                template.setTemplateMetaName(rs.getString(2));
                template.setTemplateTips(rs.getString(3));
                template.setTemplateComponent(rs.getString(4));
                template.setCompanyId(rs.getInt(5));
                template.setDepartmentId(rs.getString(6));
                template.setMultiChosen(rs.getString(7));
                template.setTemplateColor(rs.getString(8));
                template.setDefaultType(rs.getInt(9));
                template.setAmountTop(rs.getBigDecimal(10));
                template.setRequestFlow(rs.getString(11));
                template.setCc(rs.getString(12));
                template.setMaxTimePermonth(rs.getInt(13));
                template.setContentTimeLimit(rs.getInt(14));
                template.setRelaRequestNum(rs.getInt(15));
                template.setRangeSpecific(rs.getString(16));
                template.setIsAllowAddTempCc(rs.getInt(17));
                template.setIsRelatedToCostAnalysis(rs.getInt(18));
                template.setIsAllowedToEditJudger(rs.getInt(19));
                template.setVersion(rs.getInt(20));
                template.setAncestorId(rs.getInt(21));
                return template;
            } else {
                return null;
            }
        } finally {
            DBUtils.releaseResultSet(rs);
            DBUtils.releasePreparedStatement(pstm);
        }
    }

    @Override
    public List<Integer> getUsedTemplateIdsInDaysByTemplateIds(List<Template> list, List<String> days)
            throws Throwable {
        List<Integer> ids = new ArrayList<>();
        if (list == null || list.isEmpty()) {
            return ids;
        }

        StringBuilder sql = new StringBuilder(" select Frequest_templet from request_flow " +
                " where DATE_FORMAT(Fcreate_time,'%Y-%m-%d') >= ?  and DATE_FORMAT(Fcreate_time,'%Y-%m-%d') <= ? " +
                " and Frequest_templet in(");
        for (int i = 0; i < list.size(); i++) {
            sql.append(list.get(i).getTemplateId());
            if (list.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(") group by Frequest_templet order by Frequest_templet ");

        logger.info(Formatter.aformat(sql.toString(), days.get(0), days.get(1)));

        try (PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
            pstm.setString(1, days.get(0));
            pstm.setString(2, days.get(1));

            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    ids.add(rs.getInt(1));
                }
            }
        }
        return ids;
    }

    @Override
    public List<Integer> getUsedTemplateIdsInDaysByTemplateIdsAndUsers(List<Template> list,
                                                                       List<String> days, List<User> userList) throws Throwable {
        List<Integer> ids = new ArrayList<>();
        if (list == null || list.isEmpty()) {
            return ids;
        }
        StringBuilder sql = new StringBuilder(" select Frequest_templet from request_flow " +
                " where Fapplicant_ciid in(");
        for (int i = 0; i < userList.size(); i++) {
            User user = userList.get(i);
            sql.append(user.getCompanyInfoId());
            if (userList.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(") and DATE_FORMAT(Fcreate_time,'%Y-%m-%d') >= ?  " +
                " and DATE_FORMAT(Fcreate_time,'%Y-%m-%d') <= ? and Frequest_templet in(");
        for (int i = 0; i < list.size(); i++) {
            sql.append(list.get(i).getTemplateId());
            if (list.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(") group by Frequest_templet order by Frequest_templet ");
        logger.info(Formatter.aformat(sql.toString(), days.get(0), days.get(1)));
        try (PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
            pstm.setString(1, days.get(0));
            pstm.setString(2, days.get(1));
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    ids.add(rs.getInt(1));
                }
            }
        }
        return ids;
    }

    @Override
    public List<Integer> getUsedTemplateIdsInDaysByTemplateIdsAndUser(List<Template> list,
                                                                      List<String> days, User user) throws Throwable {
        List<Integer> ids = new ArrayList<>();
        if (list == null || list.isEmpty()) {
            return ids;
        }
        StringBuilder sql = new StringBuilder(" select Frequest_templet from request_flow " +
                " where Fapplicant_ciid = ? and DATE_FORMAT(Fcreate_time,'%Y-%m-%d') >= ? " +
                " and DATE_FORMAT(Fcreate_time,'%Y-%m-%d') <= ? and Frequest_templet in(");
        for (int i = 0; i < list.size(); i++) {
            sql.append(list.get(i).getTemplateId());
            if (list.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(") group by Frequest_templet order by Frequest_templet ");
        logger.info(Formatter.aformat(sql.toString(), user.getCompanyInfoId(), days.get(0), days.get(1)));
        try (PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
            pstm.setInt(1, user.getCompanyInfoId());
            pstm.setString(2, days.get(0));
            pstm.setString(3, days.get(1));

            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    ids.add(rs.getInt(1));
                }
            }
        }
        return ids;
    }

    @Override
    public void insertConditionRequestFlow(Template template, User user) throws Throwable {

        String sql = "insert into approval.pt_mp_request_condition_flow_desc set "
                + " Ftemplate_id = ?, Fcondition_type = ?, Frela_component_num = ?, Fcondition_calculation_type = ?, Fsmaller_factor = ?,"
                + " Fgreater_factor = ?, Frange = ?, Fcreate_time = NOW(), Fcompany_id = ?, Frequest_flow = ?,"
                + " Fis_allowed_to_edit_judger = ?, Funit_type = ?, Fchoose_items = ?, FLeft_symbol = ?, Fright_symbol = ? ";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            List<ConditionFlow> list = template.getConditionFlow();

            for (ConditionFlow conditionFlow : list) {
                ps.setInt(1, template.getTemplateInsertId());
                // 条件类型: 1金额,2时长
                ps.setString(2, conditionFlow.getConditionType());
                ps.setString(3, conditionFlow.getRelaComponentNum());
                // 条件计算类型: 1. 比较,2范围
                ps.setInt(4, conditionFlow.getConditionCalculationType());
                ps.setBigDecimal(5, conditionFlow.getSmallerFactor());
                ps.setBigDecimal(6, conditionFlow.getGreaterFactor());
                ps.setString(7, conditionFlow.getRange());
                ps.setInt(8, user.getCompanyId());
                ps.setString(9, conditionFlow.getRequestFlow());
                ps.setInt(10, conditionFlow.getIsAllowedToEditJudger());
                //  单位类型
                ps.setInt(11, conditionFlow.getUnitType());
                ps.setString(12, conditionFlow.getChooseItems());
                ps.setInt(13, conditionFlow.getLeftSymbol());
                ps.setInt(14, conditionFlow.getRightSymbol());

                logger.info(Formatter.aformat(sql, template.getTemplateInsertId(), conditionFlow.getConditionType(), conditionFlow.getRelaComponentNum(),
                        conditionFlow.getConditionCalculationType(), conditionFlow.getSmallerFactor(), conditionFlow.getGreaterFactor(), conditionFlow.getRange(), user.getCompanyId(),
                        conditionFlow.getRequestFlow(), conditionFlow.getIsAllowedToEditJudger(), conditionFlow.getUnitType(), conditionFlow.getChooseItems(),
                        conditionFlow.getLeftSymbol(), conditionFlow.getRightSymbol()));

                ps.addBatch();
            }
            ps.executeBatch();
        }

    }

    @Override
    public List<ConditionFlow> selectConditionFlowList(Integer templateId) throws SQLException {
        List<ConditionFlow> list = new ArrayList<>();
        String sql = " select Ftemplate_id, Fcondition_type, Frela_component_num, Fcondition_calculation_type, Fsmaller_factor, "
                + " Fgreater_factor, Frange, Fcreate_time, Fcompany_id, Frequest_flow,"
                + " Fis_allowed_to_edit_judger, Funit_type, Fchoose_items, FLeft_symbol, Fright_symbol"
                + " from pt_mp_request_condition_flow_desc where Ftemplate_id = ? ";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, templateId);
            logger.info(SQLLogger.logSQL(sql, templateId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ConditionFlow conditionFlow = new ConditionFlow();
                    conditionFlow.setTemplateId(rs.getInt(1));
                    conditionFlow.setConditionType(rs.getString(2));
                    conditionFlow.setRelaComponentNum(rs.getString(3));
                    conditionFlow.setConditionCalculationType(rs.getInt(4));
                    conditionFlow.setSmallerFactor(rs.getBigDecimal(5));
                    if (conditionFlow.getSmallerFactor() != null) {
                        conditionFlow.setSmallerFactor(conditionFlow.getSmallerFactor().setScale(0, RoundingMode.FLOOR));
                    }
                    conditionFlow.setGreaterFactor(rs.getBigDecimal(6));
                    if (conditionFlow.getGreaterFactor() != null) {
                        conditionFlow.setGreaterFactor(conditionFlow.getGreaterFactor().setScale(0, RoundingMode.FLOOR));
                    }
                    conditionFlow.setRange(rs.getString(7));
                    conditionFlow.setCreateTime(rs.getString(8));
                    conditionFlow.setCompanyId(rs.getInt(9));
                    conditionFlow.setRequestFlow(rs.getString(10));
                    conditionFlow.setIsAllowedToEditJudger(rs.getInt(11));
                    conditionFlow.setUnitType(rs.getInt(12));
                    conditionFlow.setChooseItems(rs.getString(13));
                    conditionFlow.setLeftSymbol(rs.getInt(14));
                    conditionFlow.setRightSymbol(rs.getInt(15));

                    list.add(conditionFlow);
                }
            }
        }

        return list;
    }

    @Override
    public void initMyCompanyTemplateList(Integer companyId, List<Template> defaultTemplates) throws Throwable {
        String sql = "insert into approval.request_template "
                + "(Ftemplate_name,Ftemplate_meta_name,Ftemplate_tips,Ftemplate_component,Fcompany_id,"
                + "Fdepartment_id,Fstatus,Frequest_flow,Fcc,Fmulti_chosen,"
                + "Ftemplate_color,Fversion,Ftemplate_default_type,Famount_top,Ftemplate_id,"
                + "Fis_max_time,Fmax_time_permonth,Fcontent_time_limit,Frela_request_num,Frela_people_num,"
                + "Fis_allow_add_temp_cc,Fis_related_to_cost_analysis,Fis_allowed_to_edit_judger,Fancestor_id,Fcreatetime,Fout_sign_offset) "
                + " values "
                + "(?,?,?,?,?,"
                + "0,1,?,?,?,"
                + "?,1,?,?,?,"
                + "?,?,?,?,?,"
                + "?,?,?,?,NOW(),?)";
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            for (Template template : defaultTemplates) {
                ObjectUtils.initInteger(template);
                pstm.setString(1, template.getTemplateName());
                pstm.setString(2, template.getTemplateMetaName());
                pstm.setString(3, template.getTemplateTips());
                pstm.setString(4, template.getTemplateComponent());
                pstm.setInt(5, companyId);
                pstm.setString(6, template.getRequestFlow());
                pstm.setString(7, template.getCc());
                pstm.setString(8, template.getMultiChosen());
                pstm.setString(9, template.getTemplateColor());
                pstm.setInt(10, template.getDefaultType());
                pstm.setBigDecimal(11, template.getAmountTop());
                pstm.setInt(12, template.getTemplateId());
                pstm.setInt(13, template.getIsMaxTime());
                pstm.setInt(14, template.getMaxTimePermonth());
                pstm.setInt(15, template.getContentTimeLimit());
                pstm.setInt(16, template.getRelaRequestNum());
                pstm.setInt(17, template.getRelaPeopleNum());
                pstm.setInt(18, template.getIsAllowAddTempCc());
                pstm.setInt(19, template.getIsRelatedToCostAnalysis());
                pstm.setInt(20, template.getIsAllowedToEditJudger());
                pstm.setInt(21, template.getTemplateId());
                pstm.setString(22, template.getOutSignOffset());
                logger.info("初始化:" + Formatter.aformat(sql, template.getTemplateName(), template.getTemplateMetaName(), template.getTemplateTips(),
                        template.getTemplateComponent(), companyId, template.getRequestFlow(), template.getCc(), template.getMultiChosen(),
                        template.getTemplateColor(), template.getDefaultType(), template.getAmountTop(), template.getTemplateId(), template.getIsMaxTime(),
                        template.getMaxTimePermonth(), template.getContentTimeLimit(), template.getRelaRequestNum(), template.getRelaPeopleNum(), template.getIsAllowAddTempCc(),
                        template.getIsRelatedToCostAnalysis(), template.getIsAllowedToEditJudger(), template.getTemplateId(), template.getOutSignOffset()));
                pstm.addBatch();
            }
            pstm.executeBatch();
        }
    }

    @Override
    public List<Template> getDefaultTemplateToInit() throws Throwable {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            String sql = "select Ftemplate_name,Ftemplate_meta_name,Ftemplate_default_type,Ftemplate_tips,Ftemplate_component,"
                    + " Frequest_flow,Fmulti_chosen,Ftemplate_color,Famount_top,Fis_max_time,Fmax_time_permonth,Fcontent_time_limit,"
                    + " Frela_request_num,Frela_people_num,Fis_allow_add_temp_cc,Fis_related_to_cost_analysis,Fis_allowed_to_edit_judger,Fout_sign_offset "
                    + " from approval.request_template "
                    + " where Fcompany_id = 0 and Ftemplate_default_type < 10 and Fstatus = 1  ";
            List<Template> list = new ArrayList<>();
            pstm = conn.prepareStatement(sql);
            logger.info(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Template t = new Template();
                t.setTemplateName(rs.getString(1));
                t.setTemplateMetaName(rs.getString(2));
                t.setDefaultType(rs.getInt(3));
                t.setTemplateTips(rs.getString(4));
                t.setTemplateComponent(rs.getString(5));
                t.setRequestFlow(rs.getString(6));
                t.setMultiChosen(rs.getString(7));
                t.setTemplateColor(rs.getString(8));
                t.setAmountTop(rs.getBigDecimal(9));
                t.setIsMaxTime(rs.getInt(10));
                t.setMaxTimePermonth(rs.getInt(11));
                t.setContentTimeLimit(rs.getInt(12));
                t.setRelaRequestNum(rs.getInt(13));
                t.setRelaPeopleNum(rs.getInt(14));
                t.setIsAllowAddTempCc(rs.getInt(15));
                t.setIsRelatedToCostAnalysis(rs.getInt(16));
                t.setIsAllowedToEditJudger(rs.getInt(17));
                t.setOutSignOffset(rs.getString(18));
                list.add(t);
            }
            return list;
        } finally {
            DBUtils.releaseResultSet(rs);
            DBUtils.releasePreparedStatement(pstm);
        }
    }

    @Override
    public List<Template> getTemplateUsed(User user) throws Throwable {
        String sql = "select distinct(Frequest_templet) from request_flow where Fapplicant = ? and Fcompany_id = ? and Ffinished <> -99";
        List<Integer> templateIds = new ArrayList<>();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, user.getContactId());
            pstm.setInt(2, user.getCompanyId());
            logger.info(Formatter.aformat(sql, user.getContactId(), user.getCompanyId()));
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    templateIds.add(rs.getInt(1));
                }
            }
        }
        List<Template> templates = new ArrayList<>();
        if (templateIds.size() > 0) {
            StringBuilder sb = new StringBuilder();
            sb.append("select Ftemplate_id,Fancestor_id,Ftemplate_name "
                    + "from (select Ftemplate_id,Fancestor_id,Ftemplate_name from request_template where Fancestor_id in("
                    + "select distinct(Fancestor_id) from request_template where Ftemplate_id in(");
            for (int i = 0; i < templateIds.size(); i++) {
                sb.append(templateIds.get(i));
                if (templateIds.size() - i > 1) {
                    sb.append(",");
                }
            }
            sb.append(")  and Fancestor_id <> 0 ) order by Ftemplate_id desc ) as aids group by Fancestor_id ");
            sql = sb.toString();

            try (PreparedStatement pstm = conn.prepareStatement(sql)) {
                logger.info(sql);
                try (ResultSet rs = pstm.executeQuery()) {
                    while (rs.next()) {
                        Template t = new Template();
                        t.setTemplateId(rs.getInt(1));
                        t.setAncestorId(rs.getInt(2));
                        t.setTemplateName(rs.getString(3));
                        templates.add(t);
                    }
                }
            }
        }
        return templates;
    }

    @Override
    public List<Template> getTemplateFamilyByTemplates(Template template, User user) throws Throwable {
        List<Template> templates = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select Ftemplate_name,Ftemplate_meta_name,Ftemplate_tips,Ftemplate_component,"
                + "Fcompany_id,Fdepartment_id,Fmulti_chosen,Ftemplate_color,Ftemplate_default_type,Famount_top,"
                + "Frequest_flow,Fcc,Fmax_time_permonth,Fcontent_time_limit,Frela_request_num,Frange_specific,"
                + "Fis_allow_add_temp_cc,Fis_related_to_cost_analysis,Fis_allowed_to_edit_judger,Fversion,Fancestor_id,Ftemplate_id "
                + "from approval.request_template where  ");
        if (Utils.isNull(template.getAncestorId()) || template.getAncestorId() == 0) {
            sql.append(" Ftemplate_id = ? and Fcompany_id = ? ");   // 该祖先id为0,表示老数据
        } else {
            sql.append(" Fancestor_id =? and Fcompany_id = ? ");
        }

        int selectId = Utils.isNull(template.getAncestorId()) || template.getAncestorId() == 0 ? template.getTemplateId() : template.getAncestorId();

        logger.info(Formatter.aformat(sql.toString(), selectId, user.getCompanyId()));

        try (PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
            pstm.setInt(1, selectId);
            pstm.setInt(2, user.getCompanyId());

            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Template t = new Template();
                    t.setTemplateName(rs.getString(1));
                    t.setTemplateMetaName(rs.getString(2));
                    t.setTemplateTips(rs.getString(3));
                    t.setTemplateComponent(rs.getString(4));
                    t.setCompanyId(rs.getInt(5));
                    t.setDepartmentId(rs.getString(6));
                    t.setMultiChosen(rs.getString(7));
                    t.setTemplateColor(rs.getString(8));
                    t.setDefaultType(rs.getInt(9));
                    t.setAmountTop(rs.getBigDecimal(10));
                    t.setRequestFlow(rs.getString(11));
                    t.setCc(rs.getString(12));
                    t.setMaxTimePermonth(rs.getInt(13));
                    t.setContentTimeLimit(rs.getInt(14));
                    t.setRelaRequestNum(rs.getInt(15));
                    t.setRangeSpecific(rs.getString(16));
                    t.setIsAllowAddTempCc(rs.getInt(17));
                    t.setIsRelatedToCostAnalysis(rs.getInt(18));
                    t.setIsAllowedToEditJudger(rs.getInt(19));
                    t.setVersion(rs.getInt(20));
                    t.setAncestorId(rs.getInt(21));
                    t.setTemplateId(rs.getInt(22));
                    templates.add(t);
                }
            }
        }
        return templates;
    }

    @Override
    public List<Comment> getCommentMessage(Request request, User user) throws Throwable {

        List<Comment> commentList = new ArrayList<>();
        StringBuilder sql1 = new StringBuilder();

        //得到的结果集(撤销筛选)需要进行评论类型判断,如果评论类型是1(附件),则将评论内容改为[图片],否则追加评论文本内容
        sql1.append("select Fcrt_by,Fmessage_type,Fmessage from request_comment where Frequest_id = ? and Fcompany_id = ? and Fis_cancel = 0 order by Fcrt_at");

        try (PreparedStatement pstm = conn.prepareStatement(sql1.toString())) {
            //审批的id
            pstm.setInt(1, request.getRequestDkey());
            pstm.setInt(2, user.getCompanyId());
            logger.info(Formatter.aformat(sql1.toString(), request.getRequestDkey(), user.getCompanyId()));
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Comment comment = new Comment();
                    //Fcrt_by：创建人id
                    comment.setCreateId(rs.getInt(1));
                    //Fmessage_type==1为附件
                    if (rs.getInt(2) == 1) {
                        comment.setMsg("[图片或附件]");
                    } else {
                        //Fmessage：评论内容
                        comment.setMsg(rs.getString(3));
                    }

                    commentList.add(comment);
                }
            }
        }

        return commentList;
    }

    @Override
    public GeneralRequest getCompanyGeneralFlowSettings(GeneralRequest request, User user) throws Throwable {
        String sql = "select Pid,Prequest_flow from approval.pt_mp_general_request_flow "
                + "where Pcompany_id = ? and Pproject_id = ? and Pspecific_id = ? and Fstate = 0";
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, user.getCompanyId());
            pstm.setInt(2, request.getProject());
            pstm.setString(3, request.getSpecificId());
            logger.info(Formatter.aformat(sql, user.getCompanyId(), request.getProject(), request.getSpecificId()));
            try (ResultSet rs = pstm.executeQuery()) {
                if (rs.next()) {
                    request.setGeneralFlowId(rs.getInt(1));
                    request.setRequestFlow(rs.getString(2));
                } else {
                    request.setRequestFlow(null);
                }
            }
        }
        if ("Condition".equals(request.getRequestFlow())) {
            sql = "select Fsmaller_factor,Fgreater_factor,Frequest_flow,Fcondition_calculation_type,Fcondition_type, Frange "
                    + " from approval.pt_mp_general_request_condition_flow_desc"
                    + " where Fgeneral_flow_id = ? and Fcompany_id = ? and Fstate = 0 ";
            try (PreparedStatement pstm = conn.prepareStatement(sql)) {
                pstm.setInt(1, request.getGeneralFlowId());
                pstm.setInt(2, user.getCompanyId());
                logger.info(Formatter.aformat(sql, request.getGeneralFlowId(), user.getCompanyId()));
                List<ConditionFlow> list = new ArrayList<>();
                try (ResultSet rs = pstm.executeQuery()) {
                    while (rs.next()) {
                        ConditionFlow cf = new ConditionFlow();
                        cf.setSmallerFactor(rs.getBigDecimal(1));
                        if (cf.getSmallerFactor() != null) {
                            cf.setSmallerFactor(cf.getSmallerFactor().setScale(0, RoundingMode.FLOOR));
                        }
                        cf.setGreaterFactor(rs.getBigDecimal(2));
                        if (cf.getGreaterFactor() != null) {
                            cf.setGreaterFactor(cf.getGreaterFactor().setScale(0, RoundingMode.FLOOR));
                        }
                        cf.setRequestFlow(rs.getString(3));
                        cf.setConditionCalculationType(rs.getInt(4));
                        cf.setConditionType(rs.getString(5));
                        cf.setRange(rs.getString(6));
                        list.add(cf);
                    }
                    request.setConditionFlow(list);
                }
            }
        }
        return request;
    }

    @Override
    public int saveGeneralRequestFlow(GeneralRequest request, User user)
            throws Throwable {
        String sql = "insert into approval.pt_mp_general_request_flow "
                + " set Pcompany_id = ?, Pproject_id = ?, Pspecific_id = ?, Prequest_flow = ?, Fcreate_time = NOW() "
                + " on duplicate key update Prequest_flow = VALUES(Prequest_flow), Fedit_time = NOW()";
        try (PreparedStatement pstm = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            pstm.setInt(1, user.getCompanyId());
            pstm.setInt(2, request.getProject());
            pstm.setString(3, request.getSpecificId());
            pstm.setString(4, request.getRequestFlow());
            logger.info(Formatter.aformat(sql, user.getCompanyId(), request.getProject(), request.getSpecificId(), request.getRequestFlow()));
            pstm.executeUpdate();

            try (ResultSet rs = pstm.getGeneratedKeys()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        }

        return 0;
    }

    @Override
    public void saveGeneralRequestConditionFlow(GeneralRequest request, User user)
            throws Throwable {
        String sql = "insert into approval.pt_mp_general_request_condition_flow_desc "
                + " set Fgeneral_flow_id = ?, Fcondition_type = ?, Fcondition_calculation_type = ?, Fsmaller_factor = ?, Fgreater_factor = ?, "
                + " Frange = ?, Fcreate_time = NOW(), Fcompany_id = ?, Frequest_flow = ?, Fis_allowed_to_edit_judger = ?";
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            List<ConditionFlow> list = request.getConditionFlow();
            for (ConditionFlow cf : list) {
                pstm.setInt(1, request.getGeneralFlowId());
                pstm.setString(2, cf.getConditionType());
                pstm.setInt(3, cf.getConditionCalculationType());
                pstm.setBigDecimal(4, cf.getSmallerFactor());
                pstm.setBigDecimal(5, cf.getGreaterFactor());
                pstm.setString(6, cf.getRange());
                pstm.setInt(7, user.getCompanyId());
                pstm.setString(8, cf.getRequestFlow());
                pstm.setInt(9, cf.getIsAllowedToEditJudger());
                logger.info(Formatter.aformat(sql, request.getGeneralFlowId(), cf.getConditionType(), cf.getConditionCalculationType(),
                        cf.getSmallerFactor(), cf.getGreaterFactor(), cf.getRange(), user.getCompanyId(), cf.getRequestFlow(), cf.getIsAllowedToEditJudger()));
                pstm.addBatch();
            }
            pstm.executeBatch();
        }
    }

    @Override
    public void disableGeneralRequestConditionFlow(GeneralRequest request, User user)
            throws Throwable {
        String sql = "update approval.pt_mp_general_request_condition_flow_desc set Fstate = 1 "
                + " where Fgeneral_flow_id = ? and Fcompany_id = ?";
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, request.getGeneralFlowId());
            pstm.setInt(2, user.getCompanyId());
            logger.info(Formatter.aformat(sql, request.getGeneralFlowId(), user.getCompanyId()));
            pstm.executeUpdate();
        }
    }

    @Override
    public List<Request> siftingRequestRelatedToCostAnalysis(List<Template> templateIdList, List<Request> requests) throws Throwable {
        StringBuilder selectSb = new StringBuilder("select Frequest_id "
                + " from approval.request_flow where Frequest_templet in (");
        for (int i = 0; i < templateIdList.size(); i++) {
            selectSb.append(templateIdList.get(i).getTemplateId());
            if (templateIdList.size() - i > 1) {
                selectSb.append(",");
            }
        }
        selectSb.append(")");
        List<Integer> rlist = new ArrayList<>();
        String sql = selectSb.toString();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            logger.info(sql);
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    rlist.add(rs.getInt(1));
                }
            }
        }
        List<Request> finallyRequests = new ArrayList<>();
        if (rlist.size() > 0) {
            for (Request r : requests) {
                if (rlist.contains(r.getRequestDkey())) {
                    finallyRequests.add(r);
                }
            }
        }
        return finallyRequests;
    }

    @Override
    public List<Template> getRelatedToCostAnalysisTemplateListInTheseCompanies(List<Integer> companies) throws Throwable {
        StringBuilder sb = new StringBuilder("select Ftemplate_id,Ftemplate_name,Ftemplate_color,Fversion,Ftemplate_default_type,Famount_top,Fdepartment_id,"
                + "Fancestor_id,Fmax_time_permonth,Fcontent_time_limit,Fis_max_time from approval.request_template "
                + "where Fis_related_to_cost_analysis = 1 and Fcompany_id in(");
        for (int i = 0; i < companies.size(); i++) {
            sb.append(companies.get(i).intValue());
            if (companies.size() - i > 1) {
                sb.append(",");
            }
        }
        sb.append(")");
        String sql = sb.toString();
        List<Template> list = new ArrayList<>();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            logger.info(sql);
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Template t = new Template();
                    t.setTemplateId(rs.getInt(1));
                    t.setTemplateName(rs.getString(2));
                    t.setTemplateColor(rs.getString(3));
                    t.setVersion(rs.getInt(4));
                    t.setDefaultType(rs.getInt(5));
                    t.setAmountTop(rs.getBigDecimal(6));
                    t.setDepartmentId(rs.getString(7));
                    t.setAncestorId(rs.getInt(8));
                    t.setMaxTimePermonth(rs.getInt(9));
                    t.setContentTimeLimit(10);
                    t.setIsMaxTime(rs.getInt(11));
                    list.add(t);
                }
            }
        }
        return list;
    }

    @Override
    public List<Template> getTemplateFamilyByTemplates(List<Template> list) throws Throwable {
        StringBuilder sb = new StringBuilder("select Ftemplate_id,Ftemplate_name,Ftemplate_color,Fversion,Ftemplate_default_type,"
                + " Famount_top,Fdepartment_id,Fancestor_id,Fmax_time_permonth,Fcontent_time_limit,Fis_max_time "
                + " from approval.request_template where Fancestor_id in(");
        StringBuilder condition = new StringBuilder(" and Ftemplate_id in(");
        for (int i = 0; i < list.size(); i++) {
            Template t = list.get(i);
            sb.append(t.getAncestorId());
            condition.append(t.getTemplateId());
            if (list.size() - i > 1) {
                sb.append(",");
                condition.append(",");
            }
        }
        List<Template> templates = new ArrayList<>();
        /*
         * 这里不能group by，因为很多老数据是没有祖先id的，group by 就有bug 了
         */
        sb.append(") and Fancestor_id <> 0 ");
        condition.append(")");
        sb.append(condition);
        String sql = sb.toString();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            logger.info(sql);
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Template t = new Template();
                    t.setTemplateId(rs.getInt(1));
                    t.setTemplateName(rs.getString(2));
                    t.setTemplateColor(rs.getString(3));
                    t.setVersion(rs.getInt(4));
                    t.setDefaultType(rs.getInt(5));
                    t.setAmountTop(rs.getBigDecimal(6));
                    t.setDepartmentId(rs.getString(7));
                    t.setAncestorId(rs.getInt(8));
                    t.setMaxTimePermonth(rs.getInt(9));
                    t.setContentTimeLimit(10);
                    t.setIsMaxTime(rs.getInt(11));
                    templates.add(t);
                }
            }
        }
        return templates;
    }

    @Override
    public List<Template> selectLastTemplateByRequestIds(String requestIds) throws Throwable {
        List<Template> list = new ArrayList<>();
        if (StringUtils.isEmpty(requestIds)) {
            return list;
        }
        String sql = "select Ftemplate_id, Fancestor_id, Ftemplate_name, Frelate_analysis_num, Ftemplate_color"
                + " from request_template"
                + " where Ftemplate_id in("
                + "select distinct(Frequest_templet) from request_flow where Frequest_id in(" + requestIds + ")"
                + ") group by Fancestor_id desc";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            logger.info(SQLLogger.logSQL(sql));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setAncestorId(rs.getInt(2));
                    template.setTemplateName(rs.getString(3));
                    template.setRelateAnalysisNum(rs.getString(4));
                    template.setTemplateColor(rs.getString(5));
                    list.add(template);
                }
            }
        }
        return list;
    }

    @Override
    public List<Template> selectTemplateByRequestIds(String requestIds) throws Throwable {
        List<Template> list = new ArrayList<>();
        if (StringUtils.isEmpty(requestIds)) {
            return list;
        }
        String sql = "select Ftemplate_id, Fancestor_id, Ftemplate_name, Frelate_analysis_num, Ftemplate_color"
                + " from request_template"
                + " where Ftemplate_id in("
                + "select distinct(Frequest_templet) from request_flow where Frequest_id in(" + requestIds + ")"
                + ")";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            logger.info(SQLLogger.logSQL(sql));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setAncestorId(rs.getInt(2));
                    template.setTemplateName(rs.getString(3));
                    template.setRelateAnalysisNum(rs.getString(4));
                    template.setTemplateColor(rs.getString(5));
                    list.add(template);
                }
            }
        }
        return list;
    }

    @Override
    public Template selectTemplateByRequestId(Integer requestId) throws Throwable {
        String sql = getQuerySql("where Ftemplate_id in(select Frequest_templet from request_flow where Frequest_id = ?)");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return getObject(rs);
                }
            }
        }
        return null;
    }

    @Override
    public List<Template> selectTemplatesByAncestorId(Integer ancestorId) throws Throwable {
        List<Template> list = new ArrayList<>();
        String sql = getQueryDetailSql("where Fancestor_id = ?");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, ancestorId);
            logger.info(SQLLogger.logSQL(sql, ancestorId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getDetailObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Template> getTemplateDetailByTemplateIdList(List<Integer> templateIdList) throws Throwable {
        List<Template> result = new ArrayList<>();
        StringBuilder sql = new StringBuilder(" select Ftemplate_name,Ftemplate_meta_name,Ftemplate_tips,Ftemplate_component"
                + ",Fcompany_id,Fdepartment_id,Fmulti_chosen,Ftemplate_color,Ftemplate_default_type,Famount_top"
                + ",Frequest_flow,Fcc,Fmax_time_permonth,Fcontent_time_limit,Frela_request_num,Frange_specific"
                + ",Fis_allow_add_temp_cc,Fis_related_to_cost_analysis,Fis_allowed_to_edit_judger,Fversion,Fancestor_id"
                + ",Ftemplate_id,Fcomponent_group_json from approval.request_template where Ftemplate_id in(");
        for (int i = 0; i < templateIdList.size(); i++) {
            sql.append(templateIdList.get(i));
            if (templateIdList.size() - i > 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        logger.info("导出Excel批量获取模板详情：" + sql.toString());
        try (PreparedStatement pstm = conn.prepareStatement(sql.toString())) {
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Template t = new Template();
                    t.setTemplateName(rs.getString(1));
                    t.setTemplateMetaName(rs.getString(2));
                    t.setTemplateTips(rs.getString(3));
                    t.setTemplateComponent(rs.getString(4));
                    t.setCompanyId(rs.getInt(5));

                    t.setDepartmentId(rs.getString(6));
                    t.setMultiChosen(rs.getString(7));
                    t.setTemplateColor(rs.getString(8));
                    t.setDefaultType(rs.getInt(9));
                    t.setAmountTop(rs.getBigDecimal(10));

                    t.setRequestFlow(rs.getString(11));
                    t.setCc(rs.getString(12));
                    t.setMaxTimePermonth(rs.getInt(13));
                    t.setContentTimeLimit(rs.getInt(14));
                    t.setRelaRequestNum(rs.getInt(15));       // 为-1表示不存在参与人

                    t.setRangeSpecific(rs.getString(16));
                    t.setIsAllowAddTempCc(rs.getInt(17));
                    t.setIsRelatedToCostAnalysis(rs.getInt(18));
                    t.setIsAllowedToEditJudger(rs.getInt(19));
                    t.setVersion(rs.getInt(20));

                    t.setAncestorId(rs.getInt(21));
                    t.setTemplateId(rs.getInt(22));
                    t.setComponentGroupJson(rs.getString(23));
                    result.add(t);
                }
            }
        }
        return result;
    }

    @Override
    public List<Template> getCompanyGeneralTemplateList(User user) throws Throwable {
        String sql = "select rt.Ftemplate_id, rt.Ftemplate_name, rt.Ftemplate_component, rt.Fmulti_chosen, rt.Fmoney_amount_nums, rt.Fpriority_show  "
                + " from request_template rt "
                + " where Ftemplate_id = (select max(Ftemplate_id) from request_template where Fancestor_id = rt.Fancestor_id)"
                + " and Fcompany_id = ? and Fas_general_template = 1 and Fstatus = 1 order by rt.Ftemplate_id";
        List<Template> list = new LinkedList<>();
        try (PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setInt(1, user.getCompanyId());
            logger.info(Formatter.aformat(sql, user.getCompanyId()));
            try (ResultSet rs = pstm.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setTemplateName(rs.getString(2));
                    template.setTemplateComponent(rs.getString(3));
                    template.setMultiChosen(rs.getString(4));
                    template.setMoneyAmountNums(rs.getString(5));
                    template.setPriorityShow(rs.getString(6));
                    list.add(template);
                }
            }
        }
        return list;
    }

    @Override
    public List<Template> getTemplateGroupJson(String templateIds) throws SQLException {
        List<Template> list = new ArrayList<>();

        String sql = " select Ftemplate_id, Fcompany_id, Ftemplate_default_type, Fcomponent_group_json " +
                " from request_template where Ftemplate_id in(" + templateIds + ")";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            logger.info(SQLLogger.logSQL(sql));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Template template = new Template();
                    template.setTemplateId(rs.getInt(1));
                    template.setCompanyId(rs.getInt(2));
                    template.setDefaultType(rs.getInt(3));
                    template.setComponentGroupJson(rs.getString(4));

                    list.add(template);
                }
            }
        }

        return list;
    }


}
